/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package DAO;

import Business.BillDetails_BS;
import Business.Bill_BS;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 *
 * @author mapxinh
 */
public class Bill_DAO extends ConnectionDB{

   

    Connection conn= null;
    ResultSet rs= null;
    boolean flag= false;

//     public List<Bill_BS> getBillByCustomerName(String text) {
//        ArrayList<Bill_BS> list = new ArrayList<Bill_BS>();
//        conn = Bill_DAO.getConnection();
//        try {
//           CallableStatement cs = conn.prepareCall("{call spSelectBillByCustomerName}");
//            while(rs.next()) {
//                Date datePay = rs.getDate("bDate");
//                String name = rs.getString("cName");
//                float paid = rs.getFloat("bPaid");
//                float unpaid = rs.getFloat("bUnpaid");
//                int status = rs.getInt("bStaus");
//                list.add(new Bill_BS(datePay, name, paid, unpaid,status));
//            }
//        } catch (SQLException ex) {
//            ex.printStackTrace();
//        }
//        return list;
//    }

    public List<Bill_BS> loadOrderList ()
    {
        List<Bill_BS> lst;
        lst = new ArrayList<Bill_BS>();
        conn= Bill_DAO.getConnection();
        try {
            //Statement stmt = (Statement) conn.createStatement();
            //rs= stmt.executeQuery("EXEC spSelectPurchase");
            CallableStatement cs = conn.prepareCall("{call spSelectOrder}");
            rs = cs.executeQuery();
            while(rs.next())
            {
                int bID = rs.getInt("bId");
                String cName = rs.getString("cName");
                Date bDeliveryDate = rs.getDate("bDeliveryDate");
                String bDeliveryAddress = rs.getString("bDeliveryAddress");
                float paid = rs.getFloat("bPaid");
                float unpaid = rs.getFloat("bUnpaid");
                float discount = rs.getFloat("bDiscount");
                String bOrderDate = rs.getString("bDate");
                int bStatus = rs.getInt("bStatus");
                lst.add(new Bill_BS(bID,cName,bDeliveryDate,bDeliveryAddress,paid, unpaid,discount,bOrderDate,bStatus));
            }

        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return lst;
    }
    public static List<Bill_BS> deliveryDate()
    {
        List<Bill_BS> list;
        list = new ArrayList<Bill_BS>();
        String sql = "{call spSelectDeliveryDate()}";
        try {
            //Statement stmt = (Statement) conn.createStatement();
            //rs= stmt.executeQuery("EXEC spSelectPurchase");
            CallableStatement cs = ConnectionDB.getConnection().prepareCall(sql);
            ResultSet rs = cs.executeQuery();
            while(rs.next()) {
                int bId = rs.getInt("bId");
                String name = rs.getString("cName");
                Date bDeliveryDate = rs.getDate("bDeliveryDate");
                float paid = rs.getFloat("bPaid");
                float unpaid = rs.getFloat("bUnpaid");
                int status = rs.getInt("bStatus");
                list.add(new Bill_BS(bId, name,bDeliveryDate, paid, unpaid,status));
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return list;
    }
    public List<BillDetails_BS>  loadOrderDetails(int bID) throws SQLException
   {
       List<BillDetails_BS> listor;
       listor = new ArrayList<BillDetails_BS>();
       conn= Bill_DAO.getConnection();

           CallableStatement cs= conn.prepareCall("{call spSelectOrderDetails(?)}");
           cs.setInt(1, bID);
           rs = cs.executeQuery();
           while(rs.next())
           {
               int pID = rs.getInt("pID");
               String pName= rs.getString("pName");
               float bdSellPrice = rs.getFloat("bdPrice");
               int bdQuantity = rs.getInt("bdQuantity");
               listor.add(new BillDetails_BS(pID, pName, bdSellPrice, bdQuantity));
           }
       return listor;
   }

     public int addOrder( Bill_BS b)
    {
        try
        {
            conn= Bill_DAO.getConnection();

            int cID = b.getCID();
            Date bDeliveryDate = b.getBDeliveryDate();
            String bDeliveryAddress= b.getBDeliveryAddress();
            float bDiscount = b.getBDiscount();
            float bPaid = b.getBPaid();
            float bUnpaid = b.getBUnpaid();
            String bOrderDate = b.getBdate();
            int bStatus = b.getBStatus();

            int newId = 0;

            CallableStatement cs= conn.prepareCall("{call spInsertOrder (?,?,?,?,?,?,?,?,?)}");
            cs.registerOutParameter(9, Types.INTEGER);

            cs.setInt(1, cID);
            cs.setDate(2,new java.sql.Date(bDeliveryDate.getTime()));
            cs.setString(3,bDeliveryAddress);
            cs.setFloat(4, bDiscount);
            cs.setFloat(5, bPaid);
            cs.setFloat(6, bUnpaid);
            cs.setString(7,bOrderDate);
            cs.setInt(8, bStatus);
            cs.setInt(9, newId);
            if(cs.executeUpdate() == 1)
            {
                return cs.getInt(9);
            }
        }
        catch(SQLException ex)
        {
            ex.printStackTrace();
        }
        return -1;
    }
    public boolean addOrderDetails(BillDetails_BS bd)
    {
        
        try
        {
            conn = Bill_DAO.getConnection();

            int bID= bd.getBID();
            int pID= bd.getPID();
            float bdSellPrice= bd.getBdSellPrice();
            int bdQuantity= bd.getBdQuantity();
            boolean bdReturnPro = bd.getBdReturnPro();
            String bdDate = bd.getBdDate();

            CallableStatement cs= conn.prepareCall("{call spInsertBillDetails (?,?,?,?,?)}");
            cs.setInt(1, bID);
            cs.setInt(2, pID);
            cs.setFloat(3, bdSellPrice);
            cs.setInt(4, bdQuantity);
            cs.setString(5, bdDate);

            cs.executeUpdate();

            flag= true;

        }catch(SQLException ex)
        {
            ex.printStackTrace();
        }

        return flag;
    }

    public boolean updateOrder(Bill_BS b)
    {
        conn = Bill_DAO.getConnection();
        try {
            int bId = b.getBID();
            Date bDeliveryDate = b.getBDeliveryDate();
            String bDeliveryAddress = b.getBDeliveryAddress();
            float paid = b.getBPaid();
            float unpaid = b.getBUnpaid();
            String bDate = b.getBdate();
            int bStatus = b.getBStatus();

            CallableStatement cs = conn.prepareCall("{call spUpdateOrder(?,?,?,?,?,?,?)}");
            cs.setInt(1, bId);
            cs.setDate(2, new java.sql.Date(bDeliveryDate.getTime()));
            cs.setString(3, bDeliveryAddress);
            cs.setFloat(4, paid);
            cs.setFloat(5, unpaid);
            cs.setString(6, bDate);
            cs.setInt(7, bStatus);
            cs.executeUpdate();

            flag = true;

        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return flag;
    }

    public boolean updateBuyQuantity(BillDetails_BS b)
    {
        conn = Bill_DAO.getConnection();
        try {
            int pID = b.getPID();
            int pQuantity = b.getBdQuantity();
            //System.out.println("Quantity : " + pQuantity);
            CallableStatement cs = conn.prepareCall("{call spUpdateBuyQuantity(?,?)}");
            cs.setInt(1, pID);
            cs.setInt(2, pQuantity);

            cs.executeUpdate();
            
            flag = true;
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return flag;
    }

    public static boolean remove(int productID){
            return false;
    }

    public static boolean deletOrder(int bID) {
         String sql = "{call spDeleteOrder(?)}";
        try{
            CallableStatement cs = ConnectionDB.getConnection().prepareCall(sql);

            cs.setInt(1,bID);

            return (cs.executeUpdate()==1);
        }catch(Exception ex){
            System.out.println(ex.getMessage());
            return false;
        }
    }

   
    public List<Bill_BS> searchOrderByCName(String name){
        List<Bill_BS> listBill = new ArrayList<Bill_BS>();

        try{
            CallableStatement cs = ConnectionDB.getConnection().prepareCall("{call spSearchOrderByCName(?)}");
            cs.setString(1, name);

            rs = cs.executeQuery();
            while(rs.next())
            {
                int bID = rs.getInt("bId");
                String cName = rs.getString("cName");
                Date bDeliveryDate = rs.getDate("bDeliveryDate");
                String bDeliveryAddress = rs.getString("bDeliveryAddress");
                float paid = rs.getFloat("bPaid");
                float unpaid = rs.getFloat("bUnpaid");
                float discount = rs.getFloat("bDiscount");
                String bOrderDate = rs.getString("bDate");
                int bStatus = rs.getInt("bStatus");
                listBill.add(new Bill_BS(bID,cName,bDeliveryDate,bDeliveryAddress,paid, unpaid,discount,bOrderDate,bStatus));
            }

        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return listBill;
    }

    public List<Bill_BS> searchOrder(String cusName, float price1, float price2, Date date1, Date date2) {
        List<Bill_BS> listBill = new ArrayList<Bill_BS>();
        try{
            CallableStatement cs = ConnectionDB.getConnection().prepareCall("{call spSearchOrder(?,?,?,?,?)}");
            cs.setString(1, cusName);
            cs.setFloat(2, price1);
            cs.setFloat(3, price2);
            cs.setDate(4, date1 == null ? null : new java.sql.Date(date1.getTime()));
            cs.setDate(5, date2 == null ? null : new java.sql.Date(date2.getTime()));

            rs = cs.executeQuery();
            while(rs.next())
            {
                int bID = rs.getInt("bId");
                String cName = rs.getString("cName");
                Date bDeliveryDate = rs.getDate("bDeliveryDate");
                String bDeliveryAddress = rs.getString("bDeliveryAddress");
                float paid = rs.getFloat("bPaid");
                float unpaid = rs.getFloat("bUnpaid");
                float discount = rs.getFloat("bDiscount");
                String bOrderDate = rs.getString("bDate");
                int bStatus = rs.getInt("bStatus");
                listBill.add(new Bill_BS(bID,cName,bDeliveryDate,bDeliveryAddress,paid, unpaid,discount,bOrderDate,bStatus));
            }
        } catch (Exception ex) {
            ex.printStackTrace();

        }
        return listBill;
    }
}
